﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DatabaseCore.Dto;

namespace DatabaseCore
{
    public class ProductSummaryDao : IProductSummaryDao
    {
        public List<ProductSummaryDto> GetProductSummary(string materialNo = null, string orderNo = null)
        {
            var productSummary = GetAllProductSummary().AsQueryable();

            if (!string.IsNullOrEmpty(materialNo))
            {
                productSummary = productSummary.Where(x => x.MaterialNo == materialNo);
            }

            if (!string.IsNullOrEmpty(orderNo))
            {
                productSummary = productSummary.Where(x => x.OrderNo == orderNo);
            }

            return productSummary.ToList();
        }

        #region Private method

        private List<ProductSummaryDto> GetAllProductSummary()
        {
            #region Example
            /* 
                ProdQueue
                    ProductCode	            OrderNo	        Qty
                    ZZ1100704005018018	    250214000002	900

                ProductQueueMapped
                    ProductCode	            OrderNo	        Qty
                    ZZ1100704005018018	    250214000001	900
                    ZZ1100704005018018	    250214000002	100
              
                Result
                    ProductCode	            OrderNo	        QueueQty	    QueueMappedQty
                    ZZ1100704005018018	    250214000001	0	            900
                    ZZ1100704005018018	    250214000002	900	            100
                    
             */
            #endregion

            #region SQL version
            /* 
                SELECT COALESCE(Q.ProductCode, M.ProductId) AS ProductCode
		                , COALESCE(Q.OrderNo, M.OrderNo) AS OrderNo
		                , COALESCE(Q.Qty, 0) AS QueueQty
		                , COALESCE(M.Qty, 0) AS QueueMappedQty
                FROM
                (
	                (SELECT ProductCode, OrderNo, Qty FROM dbo.ProdQueue) Q
		                FULL OUTER JOIN (SELECT ProductId, OrderNo, QTY FROM ProductQueueMapped) M 
		                ON Q.ProductCode = M.ProductId AND Q.OrderNo = M.OrderNo
                )
            */
            #endregion

            #region LINQ version

            //using (var ctx = new ExportDBEntities())
            //{
            //    var LeftOuterJoin =
            //            (
            //                from q in ctx.ProdQueue
            //                join m in ctx.ProductQueueMapped
            //                on new { q.ProductCode, q.OrderNo } equals new { ProductCode = m.ProductId, m.OrderNo } into temp
            //                from qm in temp.DefaultIfEmpty()
            //                select new
            //                {
            //                    ProductCode = q.ProductCode,
            //                    OrderNo = q.OrderNo,
            //                    QueueQty = (decimal)q.Qty,
            //                    QueueMappedQty = (decimal)0,
            //                }
            //            );

            //    var RightOuterJoin =
            //        (
            //            from m in ctx.ProductQueueMapped
            //            join q in ctx.ProdQueue
            //            on new { ProductCode = m.ProductId, m.OrderNo } equals new { q.ProductCode, q.OrderNo } into temp
            //            from qm in temp.DefaultIfEmpty()
            //            select new
            //            {
            //                ProductCode = m.ProductId,
            //                OrderNo = m.OrderNo,
            //                QueueQty = (decimal)0,
            //                QueueMappedQty = (decimal)m.QTY,
            //            }
            //        );

            //    var FullOuterJoin = LeftOuterJoin.Union(RightOuterJoin);


            //    //Final result
            //    var result =
            //    (
            //        from x in FullOuterJoin
            //        group x by new { ProductCode = x.ProductCode, OrderNo = x.OrderNo } into grp
            //        select new ProductSummaryDto
            //        {
            //            ProductCode = grp.Key.ProductCode,
            //            OrderNo = grp.Key.OrderNo,
            //            QueueQty = grp.Sum(x => x.QueueQty),
            //            QueueMappedQty = grp.Sum(x => x.QueueMappedQty)
            //        }
            //    );
            //}

            #endregion

            using (var ctx = new ExportDBEntities())
            {
                List<ProductSummaryDto> services = ctx.Database.SqlQuery<ProductSummaryDto>("SELECT * FROM [dbo].[View_ProductSummary]").ToList();
                return services;
            }
        }

        #endregion

    }
}
